
import bokeh.io
import geopandas as gpd
import numpy as np
import pandas as pd
import pandas_bokeh
from bokeh.io import output_notebook, reset_output, show
from bokeh.models import ColumnDataSource, HoverTool, NumeralTickFormatter
from bokeh.palettes import Viridis3, Viridis256, viridis
from bokeh.plotting import ColumnDataSource, figure, output_file, save, show
from bokeh.resources import INLINE
from bokeh.transform import linear_cmap

bokeh.io.output_notebook(INLINE)


# Замьютим предупреждения от shapely и определим вывод графиков в ноутбук
import warnings
from shapely.errors import ShapelyDeprecationWarning
warnings.filterwarnings("ignore", category=ShapelyDeprecationWarning) 
output_notebook()


dt_dict = {
    "general_info" : {"path" :"./PARSED DATA/general.xlsx"},
    "statistics_1" : {"path" :"./PARSED DATA/statistics_1.xlsx"},
    "statistics_2" : {"path" :"./PARSED DATA/statistics_2.xlsx"},
    "sro_membership": {"path" :"./PARSED DATA/sro.xlsx"},
    "penalties": {"path" :"./PARSED DATA/discipline.xlsx"},
}


# Читаем данные, смотрим базовую информацию
for data_name, data_name_dict in dt_dict.items():
    data_path = data_name_dict.get("path")
    data_raw = pd.read_excel(data_path)
    data_name_dict["data_raw"] = data_raw
    display(data_raw.head(3))
    display(data_raw.info())

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 40047 entries, 0 to 40046
Data columns (total 8 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   ID              40047 non-null  int64  
 1   name            39907 non-null  object 
 2   attestat        39843 non-null  object 
 3   reg_number      39907 non-null  float64
 4   date_added      31652 non-null  object 
 5   date_sro        19166 non-null  object 
 6   email           38261 non-null  object 
 7   reg_number_sro  5913 non-null   object 
dtypes: float64(1), int64(1), object(6)
memory usage: 2.4+ MB

None

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 85900 entries, 0 to 85899
Data columns (total 7 columns):
 #   Column                 Non-Null Count  Dtype
---  ------                 --------------  -----
 0   ID                     85900 non-null  int64
 1   year                   85900 non-null  int64
 2   period                 85900 non-null  int64
 3   total_decisions        85900 non-null  int64
 4   rejections_27fz        85900 non-null  int64
 5   decisions_mistakes     85900 non-null  int64
 6   decisions_suspensions  85900 non-null  int64
dtypes: int64(7)
memory usage: 4.6 MB

None

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 1048575 entries, 0 to 1048574
Data columns (total 7 columns):
 #   Column                 Non-Null Count    Dtype
---  ------                 --------------    -----
 0   ID                     1048575 non-null  int64
 1   year                   1048575 non-null  int64
 2   period                 1048575 non-null  int64
 3   total_decisions        1048575 non-null  int64
 4   rejections_27fz        1048575 non-null  int64
 5   decisions_mistakes     1048575 non-null  int64
 6   decisions_suspensions  1048575 non-null  int64
dtypes: int64(7)
memory usage: 56.0 MB

None

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 37441 entries, 0 to 37440
Data columns (total 5 columns):
 #   Column           Non-Null Count  Dtype 
---  ------           --------------  ----- 
 0   ID               37441 non-null  int64 
 1   sro_name         37441 non-null  object
 2   date_sro_incl    37246 non-null  object
 3   date_sro_excl    13322 non-null  object
 4   sro_excl_reason  13479 non-null  object
dtypes: int64(1), object(4)
memory usage: 1.4+ MB

None

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5358 entries, 0 to 5357
Data columns (total 6 columns):
 #   Column                             Non-Null Count  Dtype 
---  ------                             --------------  ----- 
 0   ID                                 5358 non-null   int64 
 1   Мера ДВ                            5358 non-null   object
 2   Дата решения о применении меры ДВ  5358 non-null   object
 3   Основание применения меры ДВ       5358 non-null   object
 4   Дата начала ДВ                     5143 non-null   object
 5   Дата окончания ДВ                  4422 non-null   object
dtypes: int64(1), object(5)
memory usage: 251.3+ KB

None


# Определим словарь переименования
rename_columns_dict = {
    # Все таблицы
    "ID":"id",
    # Таблица дисциплинарных взысканий
    "Мера ДВ": "penalty_type",
    "Дата решения о применении меры ДВ": "penalty_decision_date",
    "Основание применения меры ДВ":	"penalty_decision_reason",
    "Дата начала ДВ": "penalty_start_date",
    "Дата окончания ДВ": "penalty_end_date",
    # Таблица членства в СРО
    "date_sro_incl" : "sro_inclusion_date",
    "date_sro_excl" : "sro_exclusion_date",
    "sro_excl_reason": "sro_exclusion_reason",
    # Таблица общей информации
    "date_added" : "added_date",
    "date_sro": "sro_date",
    "name": "full_name",
    # Таблица статистики
    "total_decisions": "decisions_total",
    "rejections_27fz": "decisions_27fz",
}


def clean_general_df(df_to_clean:pd.DataFrame) -> pd.DataFrame:
    """Function to clean raw general info. Returns cleaned df"""
    df_clean = (
        df_to_clean.copy()
        # Разбираем attestat на необходимые поля
        .assign(att_number = lambda x: x.attestat.str.split("_").str[0])#.str.split(" ").str[1])
        .assign(att_number = lambda x: x.att_number.str.split(" ").str[1])
        .assign(att_date = lambda x: x.attestat.str.split("дата выдачи: ").str[1])
        .drop("attestat", axis=1)
        # Разбираем ФИО. При такой реализации могут быть ошибки в нестандартных именах
        .assign(first_name = lambda x: x.name.str.split(" ").str[1])
        .assign(last_name = lambda x: x.name.str.split(" ").str[0])
        .assign(middle_name = lambda x: x.name.str.split(" ").str[-1])
        # Переименовываем колонки по словарю
        .rename(columns=rename_columns_dict)
        # Меняем формат данных 
        .assign(reg_number = lambda x: x.reg_number.astype("Int64"))
        # Меняем формат дат
        .assign(added_date = lambda x: pd.to_datetime(x.added_date, format="%d.%m.%Y", errors="ignore").dt.date)
        .assign(sro_date = lambda x: pd.to_datetime(x.sro_date, format="%d.%m.%Y", errors="ignore").dt.date)
        .assign(att_date = lambda x: pd.to_datetime(x.att_date, format="%d.%m.%Y", errors="ignore").dt.date)
    )
    return df_clean



def clean_sro_membership_df(df_to_clean:pd.DataFrame) -> pd.DataFrame:
    """Function to clean raw SRO membership info. Returns cleaned df"""
    df_clean = (
        df_to_clean.copy()
        # Переименовываем колонки по словарю
        .rename(columns=rename_columns_dict)
        # Меняем формат дат
        .assign(sro_inclusion_date = lambda x: pd.to_datetime(x.sro_inclusion_date, format="%d.%m.%Y", errors="coerce").dt.date)
        .assign(sro_exclusion_date = lambda x: pd.to_datetime(x.sro_exclusion_date, format="%d.%m.%Y", errors="coerce").dt.date)
    )
    return df_clean


def clean_penalties_df(df_to_clean:pd.DataFrame) -> pd.DataFrame:
    """Function to clean raw penalties info. Returns cleaned df"""
    df_clean = (
        df_to_clean.copy()
        # Переименовываем колонки по словарю
        .rename(columns=rename_columns_dict)
        # Меняем формат дат
        .assign(penalty_decision_date = lambda x: pd.to_datetime(x.penalty_decision_date, format="%d.%m.%Y", errors="coerce").dt.date)
        .assign(penalty_start_date = lambda x: pd.to_datetime(x.penalty_start_date, format="%d.%m.%Y", errors="coerce").dt.date)
        .assign(penalty_end_date = lambda x: pd.to_datetime(x.penalty_end_date, format="%d.%m.%Y", errors="coerce").dt.date)
        # Переводим в нижний регистр тип взыскания
        .assign(penalty_type = lambda x: x.penalty_type.str.lower())
    )
    return df_clean

def clean_statistics_df(df_to_clean:pd.DataFrame) -> pd.DataFrame:
    """Function to clean raw statistics info. Returns cleaned df"""
    df_clean = (
        df_to_clean.copy()
        # Переименовываем колонки по словарю
        .rename(columns=rename_columns_dict)
        # Создадим колонку с периодами деятельности
        .assign(statistics_period = lambda x: x.period.astype(str)+ "-"+ x.year.astype(str)) 
        .assign(statistics_period = lambda x: (pd.to_datetime(x.statistics_period, format="%m-%Y",errors="coerce") + pd.offsets.MonthEnd(0)).dt.date)
        .assign(quarter = lambda x: (x.period/3).astype("int64"))
    )
    return df_clean


# Сохраним очищенные данные в общий словарь
dt_dict["general_info"]["data_clean"] = clean_general_df(dt_dict["general_info"]["data_raw"])
dt_dict["statistics_1"]["data_clean"] = clean_statistics_df(dt_dict["statistics_1"]["data_raw"])
dt_dict["statistics_2"]["data_clean"] = clean_statistics_df(dt_dict["statistics_2"]["data_raw"])
dt_dict["sro_membership"]["data_clean"] = clean_sro_membership_df(dt_dict["sro_membership"]["data_raw"])
dt_dict["penalties"]["data_clean"] = clean_penalties_df(dt_dict["penalties"]["data_raw"])
dt_dict["statistics"] = {"data_clean": pd.concat([dt_dict["statistics_1"]["data_clean"], dt_dict["statistics_2"]["data_clean"], ])}

for k, v in dt_dict.items():
    display(v.get("data_clean").head(3))


_att_number_df = dt_dict["general_info"].get("data_clean")["att_number"]
_att_number_df = _att_number_df.str.split("-", expand=True)
_att_number_df = _att_number_df.dropna()
_att_number_df = _att_number_df.astype("int64", errors="raise")
_att_number_df = _att_number_df.rename(columns={0: "smt_0", 1: "smt_1", 2: "smt_2"})
display(_att_number_df["smt_0"].nunique())
display(_att_number_df["smt_1"].nunique())
display(_att_number_df["smt_2"].nunique())

83

7

1577


# Посчитаем количество аттестатов по регионам
regions_att_count = (
    _att_number_df.groupby(by="smt_0")
    .agg(attestat_count=("smt_0", "count"))
    .sort_values(by="attestat_count", ascending=False)
).reset_index().rename(columns={"smt_0":"region"})
regions_att_count["rank_by_count"] = regions_att_count["attestat_count"].rank(ascending=False)
regions_att_count.head(5)


source = ColumnDataSource(regions_att_count)

# Определяем цветовой mapper для раскраски в зависимости от количества аттестатов
mapper = linear_cmap(
    field_name="attestat_count",
    palette=Viridis256,
    low=min(regions_att_count.attestat_count),
    high=max(
        regions_att_count.attestat_count,
    ),
)

# Определим данные, показываемые при наведении на график
# Наведем красоту в форматах представления данных
TOOLTIPS = [
    ("Код региона", "@region"),
    ("Количество аттестатов", "@attestat_count{0.0a}"),
    ("Ранг по кол-ву аттестатов", "@rank_by_count{0o}"), #1 -> 1st, 2 -> 2nd
]

p = figure(
    plot_height=400,
    plot_width=1000,
    tooltips = TOOLTIPS,
    title = "Количество аттестатов по предполагаемому атрибуту региона"
)

p.vbar(
    x="region",
    top="attestat_count",
    color=mapper,
    source=source,
)

# Кастомизируем оси
p.xaxis.axis_label = "Код региона"
p.yaxis.formatter = NumeralTickFormatter(format='0a')
p.yaxis.axis_label = "Количество аттестатов"

show(p)


# Скачаем данные о регионах из репозитория HFLabs. Спасибо ребятам за инфо в удобном формате
region_naming = pd.read_csv(
    "https://raw.githubusercontent.com/hflabs/region/master/region.csv",
    dtype=object,
)

# Достаем необходимые поля из таблицы регионов
geoname_df = region_naming.loc[:, ["kladr_id", "geoname_name", "iso_code"]]
geoname_df["code"] = geoname_df["kladr_id"].str[0:2]
#geoname_df["iso_code"] = geoname_df["iso_code"].str.replace("-", ".")
display(geoname_df.head(3))

# Смерджим данные в датафрейм с основной информацией
general_info_clean = dt_dict["general_info"].get("data_clean").copy()
general_info_clean["att_region"] = (
    general_info_clean["att_number"].str.split("-", expand=False).str[0]
)
general_info_clean = general_info_clean.merge(
    geoname_df[["geoname_name", "code", "iso_code"]],
    how='left',
    left_on="att_region",
    right_on="code",
).drop("code", axis=1)

# Посмотрим на результат и сохраним в словаре с данными
display(general_info_clean.head(3))
dt_dict["general_info"]["data_clean"]  = general_info_clean


statistics_df = dt_dict["statistics"]["data_clean"]
cols_to_sum = ["decisions_27fz", "decisions_mistakes", "decisions_suspensions"]
statistics_df["rejections_total"] = statistics_df[cols_to_sum].sum(axis=1)
statistics_df["acceptions_total"] = statistics_df["decisions_total"] - statistics_df["rejections_total"]
statistics_df["rejections_share"] = (
    statistics_df["rejections_total"] / statistics_df["decisions_total"]
)
statistics_df["acceptions_share"] = (
    statistics_df["acceptions_total"] / statistics_df["decisions_total"]
)
display(statistics_df.head(3))


# Подготовим данные для Bokeh
_df = statistics_df.replace([np.inf, -np.inf], np.nan, inplace=False).dropna()
# Посчитаем суммарное количество принятых и отвергнутых документов по кварталам
period_stat_df = (
    _df[["acceptions_total", "rejections_total", "statistics_period"]]
    .groupby("statistics_period")
    .sum()
    .reset_index()
)
period_stat_df["rejections_share"] = period_stat_df["rejections_total"] / (
    period_stat_df["rejections_total"] + period_stat_df["acceptions_total"]
)
source = ColumnDataSource(period_stat_df)

# Определим данные, показываемые при наведении на график
# И наведем красоту в форматах отображения данных
hover_1 = HoverTool(
    tooltips=[
    ("Период", "@statistics_period{%m-%Y}"),
    ("Количество аксептов", "@acceptions_total{0.0a}"),
    ("Количество отказов", "@rejections_total{0.0a}"),
    ("Доля отказов", "@rejections_share{0.0%}"),
],
    formatters={
        "@statistics_period"        : 'datetime', # use 'datetime' formatter for '@date' field
    },
)

# Возьмем 2 цвета из палитры Viridis
colors = viridis(2)

# Определяем график
p = figure(
    width=1000,
    height=400,
    title="Поквартальное (накопленное за год) количество одобренных и отвегнутых документов",
    toolbar_location=None,
    x_axis_type="datetime",
    tools=[hover_1],
)

# Добавляем на график данные
p.vbar_stack(
    ["acceptions_total", "rejections_total"],
    # Ось Х - это ось времени, где базовая единица миллисекунда.
    # Поэтому ширину столбцов необходимо указывать достаточно большую
    width=5e9,
    x="statistics_period",
    color=colors,
    source=source,
)

# Кастомизируем названия осей
p.xaxis.axis_label = "Период"
p.yaxis.formatter = NumeralTickFormatter(format='0.0a')
p.yaxis.axis_label = "Количество документов"

show(p)


# Подготовим данные дополнив расчетом доли принятых документов
period_stat_df["acceptions_share"] = period_stat_df["acceptions_total"] / (
    period_stat_df["rejections_total"] + period_stat_df["acceptions_total"]
)

source_2 = ColumnDataSource(period_stat_df)

# Определим данные, показываемые при наведении на график
# И наведем красоту в форматах отображения данных
hover_2 = HoverTool(
    tooltips=[
    ("Период", "@statistics_period{%m-%Y}"),
    #("Количество аксептов", "@acceptions_total{0.0a}"),
    #("Количество отказов", "@rejections_total{0.0a}"),
    ("Доля отказов", "@rejections_share{0.0%}"),
    ("Доля акцептов", "@acceptions_share{0.0%}"),
],
    formatters={
        "@statistics_period"        : 'datetime', # use 'datetime' formatter for '@date' field
    },
)

# Возьмем 2 цвета из палитры Viridis
colors = viridis(2)

# Определяем график
p2 = figure(
    width=1000,
    height=400,
    title="Поквартальная (накопленная за год) доля одобренных и отвегнутых документов",
    toolbar_location=None,
    x_axis_type="datetime",
    tools=[hover_2],
)

# Добавляем на график данные
p2.vbar_stack(
    ["acceptions_share", "rejections_share"],
    # Ось Х - это ось времени, где базовая единица миллисекунда.
    # Поэтому ширину столбцов необходимо указывать достаточно большую
    width=5e9,
    x="statistics_period",
    color=colors,
    source=source_2,
)

# Кастомизируем названия осей
p2.xaxis.axis_label = "Период"
p.yaxis.formatter = NumeralTickFormatter(format='0%')
p2.yaxis.axis_label = "Доля документов"

show(p2)


import pandas as pd
import requests
import geopandas as gpd
from osm2geojson import json2geojson

# Создадим запрос административных границ регионов
overpass_url = "http://overpass-api.de/api/interpreter"
overpass_query = """
    [out:json];
    rel[admin_level=4]
    [type=boundary]
    [boundary=administrative]
    ["ISO3166-2"~"^RU"];
    out geom;
    """
# Запрашиваем данные и формируем GeoDataFrame
response = requests.get(overpass_url, 
                        params={'data': overpass_query})
response.raise_for_status()
data = response.json()
geojson_data = json2geojson(data)
gdf_osm = gpd.GeoDataFrame.from_features(geojson_data) 

# Конвертируем словари тэгов ответа на запрос в колонки
df_tags = gdf_osm["tags"].apply(pd.Series)

# Определим, какие колонки оставить для дальнейшего анализа
# По сути - удалим переводы наименовая регионов на разные языки, оставив ru и en
cols_keep = []
for col in list(df_tags.columns):
    if "name:" not in col:
        cols_keep.append(col)
cols_keep.extend(["name:en", "name:ru"])

# Получим финальный геодатафрейм с нужными колонками
gdf_full = pd.concat([gdf_osm, df_tags.loc[:,cols_keep]], axis=1)
display(gdf_full.head())

/Users/paskin/Dev/rosreestr_parser/venv_rosreestr/lib/python3.9/site-packages/osm2geojson/main.py:514: ShapelyDeprecationWarning: Iteration over multi-part geometries is deprecated and will be removed in Shapely 2.0. Use the `geoms` property to access the constituent parts of a multi-part geometry.
  for line in merged_line:


# Установим координатную систему Coordinate Reference System (CRS)
gdf_full_mercator = gdf_full.set_crs('epsg:4326')

gdf_full_mercator.plot_bokeh(
    figsize = (1000, 600),
    simplify_shapes=20000,
    hovertool_columns=["name:ru"],
    title="Пустая карта РФ",
    xlim=[20, 180],
    ylim=[40, 80],

)


_df = statistics_df.replace([np.inf, -np.inf], np.nan, inplace=False).dropna()
_df_general = dt_dict["general_info"]["data_clean"]
_df = _df.merge(_df_general, how="left", on="id")
_df.head(3)

_df3 = (_df[["decisions_total","acceptions_total","rejections_total", "year", "att_region","iso_code"]]
.groupby(["year", "iso_code"])
.sum())

# Пересчитаем доли на агрегатах
_df3["rejections_share"] = (
    _df3["rejections_total"] / _df3["decisions_total"]
)
_df3["acceptions_share"] = (
    _df3["acceptions_total"] / _df3["decisions_total"]
)

annual_reg_stat = _df3.reset_index()
display(annual_reg_stat.head(3))


reg_stat_2021 = annual_reg_stat.loc[annual_reg_stat["year"]==2021,]
reg_stat_2021 = reg_stat_2021.replace("",np.nan).dropna()
points_to_map = gdf_full_mercator.merge(reg_stat_2021, how="left", left_on="ISO3166-2", right_on="iso_code")


#Replace NaN values to string 'No data'.
points_to_map.loc[:,["year","decisions_total","acceptions_total","rejections_total", "rejections_share"]].fillna('No data', inplace = True)
points_to_map.head()


plot_total_counts = points_to_map.plot_bokeh(
    figsize = (1000, 600),
    simplify_shapes=20000,
    hovertool_columns=["name:ru", "decisions_total","acceptions_total","rejections_total", "rejections_share"],
    dropdown = ["decisions_total","acceptions_total","rejections_total"],
    title="2021",
    colormap="Viridis",
    colorbar_tick_format="0.0a",
    xlim=[20, 180],
    ylim=[40, 80],
    return_html=True,
    show_figure=True,
)

# Export the HTML string to an external HTML file and show it:
with open("plot_total_counts.html", "w") as f:
    f.write((r"""""" + plot_total_counts))


plot_rejections = points_to_map.plot_bokeh(
    figsize=(1000, 600),
    simplify_shapes=20000,
    hovertool_columns=[
        "name:ru",
        "decisions_total",
        "acceptions_total",
        "rejections_total",
        "rejections_share",
    ],
    dropdown=["rejections_share"],
    title="2021",
    colormap="Viridis",
    colorbar_tick_format="0%",
    xlim=[20, 180],
    ylim=[40, 80],
    return_html=True,
    show_figure=True,
)

# Export the HTML string to an external HTML file and show it:
with open("plot_rejections.html", "w") as f:
    f.write(r"""""" + plot_rejections)


display(annual_reg_stat.head())
statistics_df_wide = annual_reg_stat.pivot(index="iso_code", columns=["year",])
# Убираем мультииндекс и объединяем название колонок с годами
statistics_df_wide.columns = ['_'.join((col[0],str(col[1]))) for col in statistics_df_wide.columns]
statistics_df_wide.reset_index(inplace=True)
statistics_df_wide.head()


# Replace NaN values to string 'No data'.
statistics_df_wide.fillna('No data', inplace = True)

# Combine statistics with geodataframe
history_to_map = gdf_full_mercator.merge(statistics_df_wide, how="left", left_on="ISO3166-2", right_on="iso_code")

#Specify slider columns:
slider_columns = ["rejections_share_%d"%i for i in range(2019, 2022)]

#Specify slider columns:
slider_range = range(2019, 2022)

plot_rejections_slider = history_to_map.plot_bokeh(
    figsize = (1000, 600),
    simplify_shapes=20000,
    hovertool_columns=["name:ru"]+slider_columns,  
    slider=slider_columns,
    slider_range=slider_range,
    slider_name="Year",
    title="Изменение доли отказов по регионам/годам",
    colormap="Viridis",
    colorbar_tick_format="0%",
    xlim=[20, 180],
    ylim=[40, 80],
    return_html=True,
    show_figure=True,
)

# Export the HTML string to an external HTML file and show it:
with open("plot_rejections_slider.html", "w") as f:
    f.write(r"""""" + plot_rejections_slider)


# Объединим датасеты статистики работы и общей информации по кад.инженерам
kadeng_stat = statistics_df.copy()
_df_general = dt_dict["general_info"]["data_clean"]
kadeng_stat = kadeng_stat.merge(_df_general, how="left", on="id")

# Сгруппируем данные по кадастровым инженерам и годам
kadeng_stat_agg = (kadeng_stat[["decisions_total","acceptions_total","rejections_total", "year","id", "att_region"]]
.groupby(["att_region","id", "year"])
.sum())

# Пересчитаем доли на агрегатах
kadeng_stat_agg["rejections_share"] = (
    kadeng_stat_agg["rejections_total"] / kadeng_stat_agg["decisions_total"]
)
kadeng_stat_agg["acceptions_share"] = (
    kadeng_stat_agg["acceptions_total"] / kadeng_stat_agg["decisions_total"]
)

kadeng_stat_agg.replace([np.inf, -np.inf], np.nan, inplace=True)
kadeng_stat_agg = kadeng_stat_agg.reset_index(drop=False)


kadeng_stat_agg.loc[kadeng_stat_agg["year"] == 2021].describe()


decisions_total_hist = kadeng_stat_agg.loc[kadeng_stat_agg["year"] == 2021].plot_bokeh(
    kind="hist",
    bins = 100,
    y=["decisions_total"],
    xlim=(0, 3000),
    vertical_xlabel=True,
    show_average = True,
    title = "РФ_2021: Количество поданных документов",
    show_figure=False,

)

rejections_share_hist = kadeng_stat_agg.loc[kadeng_stat_agg["year"] == 2021].dropna().plot_bokeh(
    kind="hist",
    bins=np.arange(0, 3.5, 0.1),
    y="rejections_share",
    xlim=(0, 2),
    vertical_xlabel=True,
    show_average = True,
    title = "РФ_2021: Доля отказов",
    show_figure=False,
)

plot_kad_eng_stat = pandas_bokeh.plot_grid([[decisions_total_hist, rejections_share_hist]], width=400, height=300, return_html=True,)


# Export the HTML string to an external HTML file and show it:
with open("plot_kad_eng_stat.html", "w") as f:
    f.write(r"""""" + plot_kad_eng_stat)


def plot_hist_by_region(year, region_num, kadeng_stat_agg):

    if isinstance(region_num, int):
        region_num=str(region_num)

    _decisions_total_hist = kadeng_stat_agg.loc[((kadeng_stat_agg["year"] == year) & (kadeng_stat_agg["att_region"] == region_num))].plot_bokeh(
        kind="hist",
        bins = 30,
        y=["decisions_total"],
        #xlim=(0, 3000),
        vertical_xlabel=True,
        show_average = True,
        title = f"{region_num}_{year}: Количество поданных документов", #"РФ 2021: Количество поданных документов",
        show_figure=False,
    )

    _rejections_share_hist = kadeng_stat_agg.loc[((kadeng_stat_agg["year"] == year) & (kadeng_stat_agg["att_region"] == region_num))].dropna().plot_bokeh(
        kind="hist",
        #bins=np.arange(0, 2.5, 0.1),
        bins = 30,
        y=["rejections_share"],
        #xlim=(0, 2.5),
        vertical_xlabel=True,
        show_average = True,
        title = f"{region_num}_{year}: Доля отказов",
        show_figure=False,
    )
    return [_decisions_total_hist, _rejections_share_hist]

plots_list = plot_hist_by_region(2021, 50, kadeng_stat_agg)
plot_kad_eng_stat_50 = pandas_bokeh.plot_grid([plots_list], width=400, height=300, return_html=True,)


# Export the HTML string to an external HTML file and show it:
with open("plot_kad_eng_stat_50.html", "w") as f:
    f.write(r"""""" + plot_kad_eng_stat_50)


years = [2019, 2020, 2021]
region_num = "50"

kadeng_stat_50 = kadeng_stat_agg.loc[((kadeng_stat_agg["year"].isin(years) ) & (kadeng_stat_agg["att_region"] == region_num))]

# Переведем в wide форму
kadeng_stat_50_wide = kadeng_stat_50.pivot(index=["att_region", "id"], columns=["year",])
# Убираем мультииндекс и объединяем название колонок с годами
kadeng_stat_50_wide.columns = ['_'.join((col[0],str(col[1]))) for col in kadeng_stat_50_wide.columns]
kadeng_stat_50_wide.reset_index(inplace=True)

# Дополним данными ФИО и аттестата кад.инженера
kadeng_stat_50_wide = kadeng_stat_50_wide.merge(general_info_clean, how="left", left_on="id", right_on="id")
kadeng_stat_50 = kadeng_stat_50.merge(general_info_clean, how="left", left_on="id", right_on="id")
display(kadeng_stat_50_wide.head())
display(kadeng_stat_50.head())


cols_to_plot = [
    "decisions_total",
    "acceptions_total",
    "rejections_total",
    "rejections_share",
    "full_name",
    "att_number",
    "att_date",
    "year",
]

#Use the field name of the column source
mapper = linear_cmap(field_name='year', palette=Viridis3, low=2019 ,high=2021)

source = ColumnDataSource(data=kadeng_stat_50.loc[:, cols_to_plot])

TOOLTIPS = [
    ("Год", "@year"),
    ("ФИО", "@full_name"),
    ("Всего решений", "@decisions_total"),
    ("Доля отказов", "@rejections_share{(0%)}"),
    ("Аттестат", "@att_number"),
]

p = figure(width=800, height=400, tooltips=TOOLTIPS, title="Количество решений Росреестра: всего и отказов",)

p.circle(
    "rejections_total",
    "decisions_total",
    source=source,
    color = mapper,
    legend_group = "year"
)

p.legend.location = "top_left"
p.xaxis.axis_label = "Количество отказов"
p.xaxis.formatter = NumeralTickFormatter(format='0')
p.yaxis.axis_label = "Количество решений"

# Output to file / notebook
reset_output()
#output_file("plot_kad_eng_50_2019_2021.html")
#save(p)
output_notebook()
show(p)


cols_to_plot = ["decisions_total", "acceptions_total", "rejections_total",  "rejections_share", "full_name", "att_number", "att_date", "year", ]
kadeng_stat_50.loc[:,cols_to_plot]


kadeng_stat_50_wide.head(3)

# Определим правила ренкинга: для колонок где "меньше-лучше" используем параметр ascending = True
cols_to_rank = {
    "decisions_total": False,
    "acceptions_total": False,
    "rejections_share": True,
}
years_to_rank = [2019, 2020, 2021]

# Функция создания колонок с рангом по разным показателям/годам
def rank_kad_eng(df, cols_to_rank, years_to_rank):
    for year in years_to_rank:
        for col, asc_param in cols_to_rank.items():
            df[f"rank_{col}_{year}"] = df[f"{col}_{year}"].rank(
                na_option="bottom",
                ascending=asc_param,
                method="min",
            )
    return df


kadeng_stat_50_ranked = rank_kad_eng(kadeng_stat_50_wide, cols_to_rank, years_to_rank)

# Определим интегральный взвешенный показатель, учитывающий активность и доли отказов
# Веса определил исходя из своего видения важности критериев
# Для своих целей я решил 2019 не использовать, его вес будет 0

ranking_weights = {
    # Sum to 1
    "years_weights": {
        2019: 0,
        2020: 0.25,
        2021: 0.75,
    },
    # Sum to 1
    "indicator_weights": {
        "decisions_total": 0.25,
        "rejections_share": 0.75,
        "acceptions_total": 0,
    },
}


def integral_rank(df, ranking_weights):
    df["integral_rank"] = 0
    for year, year_weight in ranking_weights.get("years_weights").items():
        for indicator, ind_weight in ranking_weights.get("indicator_weights").items():
            df["integral_rank"] = (
                df["integral_rank"]
                + df[f"rank_{indicator}_{year}"] * year_weight * ind_weight
            )
    return df

kadeng_stat_50_integral = integral_rank(kadeng_stat_50_ranked, ranking_weights)


display(
    kadeng_stat_50_integral.sort_values(by="integral_rank").head(10).T
)

	ID	name	attestat	reg_number	date_added	date_sro	email	reg_number_sro
0	826933	Ёжиков Роман Дмитриевич	номер: 13-11-56_x000D_\n дата выдачи: 07....	9624.0	03.03.2011	30.06.2016	ezikoff@mail.ru	NaN
1	816193	Ёжикова Анастасия Игоревна	номер: 23-15-1421_x000D_\n дата выдачи: 1...	34341.0	01.07.2015	26.11.2016	NaN	NaN
2	817155	Ёлчин Евгений Владиславович	номер: 50-11-720_x000D_\n дата выдачи: 27...	16912.0	06.10.2011	28.06.2016	yolchin@mail.ru	NaN

	ID	year	period	total_decisions	rejections_27fz	decisions_mistakes
0	826933	2014	9	136	1	2
1	826933	2014	12	215	2	2
2	826933	2015	3	57	2	2

	ID	Мера ДВ	Дата решения о применении меры ДВ	Основание применения меры ДВ	Дата начала ДВ	Дата окончания ДВ
0	824174	Замечание	03.12.2020	Протокол ДК № 62д/12	03.12.2020	03.12.2020
1	824174	Замечание	10.02.2022	Протокол Дисциплинарной комиссии Ассоциации СР...	10.02.2022	10.02.2022
2	812881	предписание устранить нарушение в срок до 11.0...	12.05.2021	Протокол заседания Дисциплинарного комитета А ...	12.05.2021	11.06.2021

	id	full_name	reg_number	added_date	sro_date	email	reg_number_sro	att_number	att_date	first_name	last_name	middle_name
0	826933	Ёжиков Роман Дмитриевич	9624	2011-03-03	2016-06-30	ezikoff@mail.ru	NaN	13-11-56	2011-02-07	Роман	Ёжиков	Дмитриевич
1	816193	Ёжикова Анастасия Игоревна	34341	2015-07-01	2016-11-26	NaN	NaN	23-15-1421	2015-06-17	Анастасия	Ёжикова	Игоревна
2	817155	Ёлчин Евгений Владиславович	16912	2011-10-06	2016-06-28	yolchin@mail.ru	NaN	50-11-720	2011-09-27	Евгений	Ёлчин	Владиславович

	id	year	period	decisions_total	decisions_27fz	decisions_mistakes	statistics_period	quarter
0	826933	2014	9	136	1	2	2014-09-30	3
1	826933	2014	12	215	2	2	2014-12-31	4
2	826933	2015	3	57	2	2	2015-03-31	1

Содержание¶

Для кого эта статья¶

UPD:¶

Предыстория¶

Данные Росреестра¶

Приступаем к анализу¶

Предобработка данных¶

Анализ¶

Анализ выданных аттестатов кад.инженеров¶

Анализ статистики деятельности кад.инженеров¶

Геовизуализация¶

Анализ общего количества документов¶

Анализ отказов¶

Анализ работы индивидуальных инженеров¶

Выбор лучших кад.инженеров по Московской области¶

Предварительные выводы¶

Допущения и ограничения анализа¶

Следующие шаги¶

	ID	sro_name	date_sro_incl	date_sro_excl	sro_excl_reason
0	826933	Ассоциация Саморегулируемая организация "Межре...	30.06.2016	NaN	NaN
1	816193	Саморегулируемая организация Ассоциация "Неком...	26.11.2016	NaN	NaN
2	817155	Ассоциация "Гильдия кадастровых инженеров"	28.06.2016	NaN	NaN

	id	year	period	decisions_total	decisions_suspensions	statistics_period	quarter
0	832760	2020	3	49	0	2020-03-31	1
1	832760	2020	6	135	8	2020-06-30	2
2	832760	2020	9	177	12	2020-09-30	3

	id	penalty_type	penalty_decision_date	penalty_decision_reason	penalty_start_date	penalty_end_date
0	824174	замечание	2020-12-03	Протокол ДК № 62д/12	2020-12-03	2020-12-03
1	824174	замечание	2022-02-10	Протокол Дисциплинарной комиссии Ассоциации СР...	2022-02-10	2022-02-10
2	812881	предписание устранить нарушение в срок до 11.0...	2021-05-12	Протокол заседания Дисциплинарного комитета А ...	2021-05-12	2021-06-11

	kladr_id	geoname_name	iso_code	code
0	0100000000000	Adygeya Republic	RU-AD	01
1	0200000000000	Bashkortostan Republic	RU-BA	02
2	0300000000000	Buryatiya Republic	RU-BU	03

	geometry	type	id	tags	ISO3166-2	addr:country	admin_level	border_type	boundary	cadaster:code	...	place	source:population	alt_name2	old_name	gis-lab:status	source:url	country	ref:en	name:en	name:ru
0	MULTIPOLYGON (((35.14891 55.95777, 35.14850 55...	relation	51490	{'ISO3166-2': 'RU-MOS', 'addr:country': 'RU', ...	RU-MOS	RU	4	region	administrative	50	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Moscow Oblast	Московская область
1	MULTIPOLYGON (((38.67446 54.25787, 38.66852 54...	relation	71950	{'ISO3166-2': 'RU-RYA', 'addr:country': 'RU', ...	RU-RYA	RU	4	region	administrative	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Ryazan Oblast	Рязанская область
2	MULTIPOLYGON (((37.73038 52.60995, 37.72625 52...	relation	72169	{'ISO3166-2': 'RU-LIP', 'addr:country': 'RU', ...	RU-LIP	RU	4	region	administrative	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Lipetsk Oblast	Липецкая область
3	MULTIPOLYGON (((39.91569 52.70885, 39.92159 52...	relation	72180	{'ISO3166-2': 'RU-TAM', 'addr:country': 'RU', ...	RU-TAM	RU	4	region	administrative	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Tambov Oblast	Тамбовская область
4	MULTIPOLYGON (((38.14031 51.63704, 38.14045 51...	relation	72181	{'ISO3166-2': 'RU-VOR', 'addr:country': 'RU', ...	RU-VOR	RU	4	region	administrative	NaN	...	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	Voronezh Oblast	Воронежская область

	year	iso_code	decisions_total	acceptions_total	rejections_total	rejections_share	acceptions_share
0	2014	RU-AD	90406	88318	2088	0.023096	0.976904
1	2014	RU-AL	18140	17853	287	0.015821	0.984179
2	2014	RU-ALT	77748	76185	1563	0.020103	0.979897

	iso_code	decisions_total_2014	decisions_total_2015	decisions_total_2016	decisions_total_2017	decisions_total_2018	decisions_total_2019	decisions_total_2020	decisions_total_2021	acceptions_total_2014	...	rejections_share_2020	rejections_share_2021	acceptions_share_2014	acceptions_share_2015	acceptions_share_2016	acceptions_share_2017	acceptions_share_2018	acceptions_share_2019	acceptions_share_2020	acceptions_share_2021
0	RU-AD	90406	113177	118257	80315	112610	101365	103318	103807	88318	...	0.044813	0.059052	0.976904	0.984573	0.985608	0.995032	0.994379	0.948651	0.955187	0.940948
1	RU-AL	18140	29376	25000	18354	16829	15671	17242	28678	17853	...	0.204849	0.075493	0.984179	0.987405	0.973400	0.997984	0.997980	0.897199	0.795151	0.924507
2	RU-ALT	77748	144355	121379	90999	109766	108327	69803	87897	76185	...	0.032936	0.025678	0.979897	0.989089	0.993714	0.999165	0.998506	0.969066	0.967064	0.974322
3	RU-AMU	61431	80261	67780	53181	52099	39958	37574	45157	60253	...	0.062463	0.082889	0.980824	0.973935	0.966421	0.994697	0.994760	0.923545	0.937537	0.917111
4	RU-ARK	45471	56499	51441	50128	42273	6389	14047	36597	45277	...	0.218766	0.073749	0.995734	0.997416	0.998425	0.998125	0.996097	0.741744	0.781234	0.926251

	id	year	decisions_total	acceptions_total	rejections_total	rejections_share	acceptions_share
count	39621.000000	39621.0	39621.000000	39621.00000	39621.000000	19987.000000	19987.000000
mean	824274.963580	2021.0	148.937760	129.19843	19.739330	0.247760	0.752240
std	11646.219417	0.0	436.343914	417.77568	65.527005	0.721857	0.721857
min	804223.000000	2021.0	0.000000	-618.00000	0.000000	0.000000	-36.666667
25%	814220.000000	2021.0	0.000000	0.00000	0.000000	0.000000	0.769231
50%	824232.000000	2021.0	1.000000	0.00000	0.000000	0.065068	0.934932
75%	834221.000000	2021.0	155.000000	121.00000	10.000000	0.230769	1.000000
max	850716.000000	2021.0	28630.000000	28511.00000	3323.000000	37.666667	1.000000

	att_region_x	id	decisions_total_2019	decisions_total_2020	decisions_total_2021	acceptions_total_2019	acceptions_total_2020	acceptions_total_2021	rejections_total_2019	rejections_total_2020	...	email	reg_number_sro	att_number	att_date	first_name	last_name	middle_name	att_region_y	geoname_name	iso_code
0	50	804422	424	241	107	316	124	-12	108	117	...	509-05-35@mail.ru	NaN	50-11-300	2011-01-25	Галина	Армеева	Алексеевна	50	Moscow Oblast	RU-MOS
1	50	804463	398	69	75	166	6	-16	232	63	...	9440707@mail.ru	NaN	50-10-245	2010-12-28	Дмитрий	Абрамов	Александрович	50	Moscow Oblast	RU-MOS
2	50	804474	26	10	0	15	7	0	11	3	...	ss00@km.ru	NaN	50-13-920	2013-07-24	Марина	Анохина	Владимировна	50	Moscow Oblast	RU-MOS
3	50	804484	161	163	184	37	75	-22	124	88	...	RasadkinaAnna@inbox.ru	NaN	50-11-659	2011-07-05	Анна	Безрукавникова	Павловна	50	Moscow Oblast	RU-MOS
4	50	804581	390	151	84	179	110	-24	211	41	...	kate-wolf@yandex.ru	NaN	50-11-302	2011-01-25	Екатерина	Волкова	Леонидовна	50	Moscow Oblast	RU-MOS

	att_region_x	id	year	decisions_total	acceptions_total	rejections_total	rejections_share	acceptions_share	full_name	reg_number	...	email	reg_number_sro	att_number	att_date	first_name	last_name	middle_name	att_region_y	geoname_name	iso_code
0	50	804422	2019	424	316	108	0.254717	0.745283	Армеева Галина Алексеевна	6598	...	509-05-35@mail.ru	NaN	50-11-300	2011-01-25	Галина	Армеева	Алексеевна	50	Moscow Oblast	RU-MOS
1	50	804422	2020	241	124	117	0.485477	0.514523	Армеева Галина Алексеевна	6598	...	509-05-35@mail.ru	NaN	50-11-300	2011-01-25	Галина	Армеева	Алексеевна	50	Moscow Oblast	RU-MOS
2	50	804422	2021	107	-12	119	1.112150	-0.112150	Армеева Галина Алексеевна	6598	...	509-05-35@mail.ru	NaN	50-11-300	2011-01-25	Галина	Армеева	Алексеевна	50	Moscow Oblast	RU-MOS
3	50	804463	2019	398	166	232	0.582915	0.417085	Абрамов Дмитрий Александрович	3064	...	9440707@mail.ru	NaN	50-10-245	2010-12-28	Дмитрий	Абрамов	Александрович	50	Moscow Oblast	RU-MOS
4	50	804463	2020	69	6	63	0.913043	0.086957	Абрамов Дмитрий Александрович	3064	...	9440707@mail.ru	NaN	50-10-245	2010-12-28	Дмитрий	Абрамов	Александрович	50	Moscow Oblast	RU-MOS

	region	attestat_count	rank_by_count
0	77	2260	1.0
1	23	1366	2.0
2	2	1237	3.0
3	50	1164	4.0
4	78	983	5.0

	730	984	1047	143	741	636	210	1018	1051	771
att_region_x	50	50	50	50	50	50	50	50	50	50
id	830041	837967	840193	809079	830523	826998	811267	839254	840316	831422
decisions_total_2019	728	1810	877	184	83	665	1517	1459	720	74
decisions_total_2020	568	430	243	121	692	124	443	1015	499	89
decisions_total_2021	1268	417	1213	1972	385	663	466	857	503	1399
acceptions_total_2019	634	1533	744	139	68	660	1489	1114	679	46
acceptions_total_2020	556	430	231	112	671	112	428	912	474	77
acceptions_total_2021	1256	391	1180	1884	353	654	389	622	395	1267
rejections_total_2019	94	277	133	45	15	5	28	345	41	28
rejections_total_2020	12	0	12	9	21	12	15	103	25	12
rejections_total_2021	12	26	33	88	32	9	77	235	108	132
rejections_share_2019	0.129121	0.153039	0.151653	0.244565	0.180723	0.007519	0.018457	0.236463	0.056944	0.378378
rejections_share_2020	0.021127	0.0	0.049383	0.07438	0.030347	0.096774	0.03386	0.101478	0.0501	0.134831
rejections_share_2021	0.009464	0.06235	0.027205	0.044625	0.083117	0.013575	0.165236	0.274212	0.214712	0.094353
acceptions_share_2019	0.870879	0.846961	0.848347	0.755435	0.819277	0.992481	0.981543	0.763537	0.943056	0.621622
acceptions_share_2020	0.978873	1.0	0.950617	0.92562	0.969653	0.903226	0.96614	0.898522	0.9499	0.865169
acceptions_share_2021	0.990536	0.93765	0.972795	0.955375	0.916883	0.986425	0.834764	0.725788	0.785288	0.905647
full_name	Седова Юлия Витальевна	Маркин Сергей Александрович	Костикова Алла Владимировна	Лавренова Елена Юрьевна	Логиновская Ольга Андреевна	Иванова Александра Витальевна	Петухов Андрей Викторович	Гребенников Андрей Викторович	Бибиков Сергей Михайлович	Зайцева Светлана Юрьевна
reg_number	707	3070	30537	33941	702	4467	7709	2275	37980	35218
added_date	2010-12-10	2011-01-13	2014-05-08	2015-05-05	2010-12-10	2011-01-19	2011-02-16	2010-12-28	2016-06-06	2015-10-22
sro_date	NaT	2016-06-30	2016-05-25	2019-08-27	NaT	NaT	2016-09-30	NaT	2016-06-28	2020-03-20
email	Sedova_u79@mail.ru	samarkin@list.ru	pavlovoposad@mobti.ru	muumuu@mail.ru	burologinovskix@mail.ru	alex_i_83@mail.ru	andrey_2282@mail.ru	dian-kadastr@yandex.ru	s_bibikov@mail.ru	qvora@yandex.ru
reg_number_sro	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN	NaN
att_number	50-10-104	50-10-251	50-14-994	50-15-1081	50-10-99	50-11-282	50-11-448	50-10-212	50-16-1290	50-15-1098
att_date	2010-11-30	2010-12-28	2014-05-07	2015-05-05	2010-11-30	2011-01-13	2011-02-08	2010-12-21	2016-06-01	2015-10-21
first_name	Юлия	Сергей	Алла	Елена	Ольга	Александра	Андрей	Андрей	Сергей	Светлана
last_name	Седова	Маркин	Костикова	Лавренова	Логиновская	Иванова	Петухов	Гребенников	Бибиков	Зайцева
middle_name	Витальевна	Александрович	Владимировна	Юрьевна	Андреевна	Витальевна	Викторович	Викторович	Михайлович	Юрьевна
att_region_y	50	50	50	50	50	50	50	50	50	50
geoname_name	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast	Moscow Oblast
iso_code	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS	RU-MOS
rank_decisions_total_2019	145.0	20.0	109.0	458.0	575.0	162.0	32.0	35.0	148.0	585.0
rank_acceptions_total_2019	104.0	16.0	76.0	426.0	529.0	95.0	18.0	35.0	91.0	572.0
rank_rejections_share_2019	167.0	202.0	198.0	352.0	242.0	78.0	83.0	336.0	105.0	541.0
rank_decisions_total_2020	66.0	116.0	216.0	401.0	42.0	393.0	111.0	15.0	87.0	454.0
rank_acceptions_total_2020	31.0	62.0	142.0	281.0	21.0	281.0	64.0	5.0	52.0	356.0
rank_rejections_share_2020	95.0	1.0	111.0	125.0	102.0	135.0	104.0	138.0	112.0	172.0
rank_decisions_total_2021	7.0	108.0	8.0	2.0	124.0	47.0	92.0	23.0	79.0	4.0
rank_acceptions_total_2021	3.0	25.0	4.0	1.0	33.0	11.0	26.0	13.0	24.0	2.0
rank_rejections_share_2021	32.0	41.0	35.0	38.0	45.0	34.0	64.0	94.0	77.0	48.0